/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sda.levelup.jdbc.library;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author Дима
 */
public class LibManager
{

    public List<BookDB> getAllBooks() {
        LinkedList<BookDB> books = new LinkedList<BookDB>();
        try {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try{
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "1z2x3c4v");
                ps = conn.prepareStatement("Select * from library.book");
                rs = ps.executeQuery();
                BookDB book = null;
                while(rs.next()){
                    book = new BookDB(rs.getInt("id_book"), rs.getString("name"), rs.getInt("id_author"), rs.getInt("id_genre"), rs.getInt("id_publisher"));
                    books.add(book);
                }
            }
                finally
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
                if (ps != null)
                {
                    ps.close();
                    ps = null;
                }
                if (rs != null)
                {
                    rs.close();
                    rs = null;
                }
            }
            }
        catch(Exception e){
            e.printStackTrace();
        }  
        return books;
    }
    public boolean addBookDB(BookDB b){
        boolean res = false;
        try{
            Connection conn = null;
            PreparedStatement ps = null;
            try{
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "1z2x3c4v");
                ps = conn.prepareStatement("Insert library.book (`id_book`, `name`, `id_author`, `id_genre`, `id_publisher`) VALUES(?,?,?,?,?)");                
                ps.setInt(1, b.getId_book());
                ps.setString(2, b.getName());
                ps.setInt(3, b.getId_author());
                ps.setInt(4, b.getId_genre());
                ps.setInt(5, b.getId_publisher());
                int suc = ps.executeUpdate();
                res = suc>0;
            }  
            finally {
                 if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
                if (ps != null)
                {
                    ps.close();
                    ps = null;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }
    public List<BookDB> allAuthorBook(String sername){
        List<BookDB> list = new LinkedList<BookDB>();
        try{
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try{
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "1z2x3c4v");
                ps = conn.prepareStatement("SELECT * FROM book, author where book.id_author=author.id_author and author.id_author = " +
"(SELECT id_author FROM author where sername = '"+sername+"');");
                rs = ps.executeQuery();
                BookDB b = null;
                while(rs.next()){
                    b = new BookDB(rs.getInt("id_book"), rs.getString("name"), rs.getInt("id_author"), rs.getInt("id_genre"), rs.getInt("id_publisher"));
                    list.add(b);
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
                if (ps != null)
                {
                    ps.close();
                    ps = null;
                }
                if (rs != null)
                {
                    rs.close();
                    rs = null;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
    public BookDB getBookById(int id){
        BookDB b = null;
        try{
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try{
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "1z2x3c4v");
                ps = conn.prepareStatement("Select * From book where id_book = "+id+";");
                rs = ps.executeQuery();
                rs.next();
                b = new BookDB(rs.getInt("id_book"), rs.getString("name"), rs.getInt("id_author"), rs.getInt("id_genre"), rs.getInt("id_publisher"));
            }   
            finally
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
                if (ps != null)
                {
                    ps.close();
                    ps = null;
                }
                if (rs != null)
                {
                    rs.close();
                    rs = null;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return b;
    }
    public BookDB getBookByName(String name){
        BookDB b = null;
        try{
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try{
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "1z2x3c4v");
                ps = conn.prepareStatement("Select * From book where name = '"+name+"';");
                rs = ps.executeQuery();
                rs.next();
                b = new BookDB(rs.getInt("id_book"), rs.getString("name"), rs.getInt("id_author"), rs.getInt("id_genre"), rs.getInt("id_publisher"));
            }
            finally
            {
                if (conn != null)
                {
                    conn.close();
                    conn = null;
                }
                if (ps != null)
                {
                    ps.close();
                    ps = null;
                }
                if (rs != null)
                {
                    rs.close();
                    rs = null;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return b;
    }
}
